<?php
require_once(CORE_DIR.'Common.php');
require_once(CORE_DIR.'yammerModel.php');
require_once(CORE_DIR.'userModel.php');
require_once(CORE_DIR.'projectModel.php');
require_once(CORE_DIR.'dailytaskModel.php');
require_once(CORE_DIR.'mappers/TaskMapper.php');


class taskModel extends Model{
	protected $uModel;
	protected $projModel;
	protected $dailytaskModel;
	
	protected $importanceStatus = array(
		array(3, "Burning", "#f8b1b1"),
		array(2, "Important", "#d7d7fd"),
		array(1, "Normal", "#ffffff")
		);
	
	function __construct(){
		$this->conn = $this->getConnection();
		$this->uModel = new userModel();
		$this->projModel = new projectModel();
		$this->dailytaskModel = new dailytaskModel();
	}
	
	function getStatusList(){
		$statuslist = array();
		for ($i = 0; $i < count($this->importanceStatus); $i++){
			$statuslist[$i]["id"] = $this->importanceStatus[$i][0];
			$statuslist[$i]["name"] =  $this->importanceStatus[$i][1];
			$statuslist[$i]["color"] = $this->importanceStatus[$i][2];
		}
		return $statuslist;
	}	
	
	function getTaskDto($taskId)
	{
		$query = "SELECT * FROM task WHERE id = %d";
		$query = $this->format($query, array($taskId));
		$taskDtoList = $this->execute($query, new TaskMapper());
		return $taskDtoList[0];		
	}
	
	function addTask($pid, $tskName, $tskDesc, $tskImportant, $tskType, $tskStatus, $finishDate){
		$order = $this->getNewOrderByProject($pid);
		$query = "INSERT INTO task (projectId, name, creationDate, finishDate, description, important, order_id, status, statusChangedDate, author, reported, tasktype)
				  VALUES (%d, '%s', now(), '%s', '%s', %d, %d, %d, now(), %d, 0, %d)";

		$uDto = $this->uModel->getActiveUser();
		
		$query = $this->format($query, array($pid, $tskName, $finishDate, $tskDesc, $tskImportant, $order, $tskStatus, $uDto->getId(), $tskType));
		mysqli_query($this->conn, $query);		
		$task_id =  mysqli_insert_id($this->conn);
		
		return $task_id;
	}
	
	function addDailyTask($tskName, $id = "", $dailyState = 1, $date)
	{
		$enddate = date("Y-m-d H:i:s", mktime(23, 59, 59, date('m'), date('d'), date('Y')) );
		if ($id == "")
		{
			$projectId = $this->projModel->getDefaultProject();
			$insertTaskId = $this->addTask($projectId, $tskName, "", 0, 1, 0, $enddate); 
			if ($insertTaskId > 0)
				if ($this->dailytaskModel->addDailyTask($insertTaskId, 0, $date) > 0);
					return $insertTaskId; 
		}
		else{
			if ($dailyState == 1) {
				$this->dailytaskModel->addDailyTask($id, 0, $date);
			}
			else {
				$this->dailytaskModel->removeDailyTask($id);
			}
			return true;
		}
		return false;
	}
	
	function moveDailyTask($id, $date)
	{
		$enddate = date("Y-m-d H:i:s", mktime(23, 59, 59, date('m'), date('d'), date('Y')) );
		return $this->dailytaskModel->moveDailyTask($id, $date);
	}	
	
	function updateTask($id, $tskName, $tskDesc, $tskImportant, $tskType, $finishDate, $projectId, $tskStatus = 0){
		if( $tskStatus == 1 )
			$query = "update task set name='%s', description='%s', important=%d, tasktype=%d, finishDate='%s', updatedate=NOW(), status=%d, projectId=%d, statusChangedDate=now() where id=%d";
		else
			$query = "update task set name='%s', description='%s', important=%d, tasktype=%d, finishDate='%s', updatedate=NOW(), status=%d, projectId=%d where id=%d";
		$query = $this->format($query, array($tskName, $tskDesc, $tskImportant, $tskType, $finishDate, $tskStatus, $projectId, $id));
		//echo $query;
		$retVal = mysqli_query($this->conn, $query);	

		if($retVal)
		{
			if ($tskStatus == 1)
				$this->dailytaskModel->updateDailyTask($id, 1);
				
			$userList = $this->getTaskRelatedUserList($id);
			$yModel = new yammerModel();
			
			foreach ($userList as $user) {
				$userid = $user['id'];
				$uDto = $this->uModel->getUserData($userid);
				
				$params['firstname'] = $uDto->getFirstName();
				$params['lastname'] = $uDto->getLastName();
				$params['querystr'] = "tid=".$id;
				$params['_BESTNAME_'] = $uDto->getNickName();
				$params['_PATH_'] = SITE_URL_CLIENT;
				
				$yModel->incYammerDirectMessage($userid, "yammerupdatetask", $params);
			}
		}
		return $retVal;
	}

	function updateTaskReportFlag($id, $tskReported){
		$query = "update task set reported=%d where id=%d";
		$query = $this->format($query, array($tskReported, $id));		
		
		return mysqli_query($this->conn, $query);	
	}

	function updateTaskStatus($id, $pid, $tskStatus){
		if ($tskStatus == 1)
		{
			$this->dailytaskModel->updateDailyTask($id, 1);
			$order = 0;
		}
		else
			$order = $this->getNewOrderByProject($pid);
		
		$query = "update task set status=%d, statusChangedDate=now(), order_id=%d, updatedate=NOW() where id=%d";
		$query = $this->format($query, array($tskStatus, $order, $id));
		return mysqli_query($this->conn, $query);
	}	
	
	function deleteTask($id){
//		$query = "delete from task where id=%d";
		$query = "update task set deleted=1, deletedate=now() where id=%d";
		$query = $this->format($query, array($id));		
		return mysqli_query($this->conn, $query);
//		if(mysqli_query($this->conn, $query))
//			if($this->deleteTaskFromUsers($id))
//				return $this->deleteTaskComments($id);
//		return false;		
	}		
	
	function getAllTasks(){
		$query = "SELECT t.*, uauthor.nickName AS author, p.id AS projectid, p.name AS projectname, 
		u.id AS userid, UPPER(u.nickName) AS username, tp.pointTypeID, tp.point FROM task t
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author 
				WHERE p.archstate=0 and t.deleted=0
				ORDER BY t.important DESC, t.order_id ";		
		$result = mysqli_query($this->conn, $query);				
		
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['name'] = $row['name'];
			$list[$row['id']]['author'] = $row['author'];
			$list[$row['id']]['date'] = date("d M, Y H:i", strtotime($row['creationDate']));
			$list[$row['id']]['finish_date'] = $row['finishDate'];
			$list[$row['id']]['finish_date_left'] = timeLeft($row['finishDate']);
			$list[$row['id']]['dateago'] = $row['status'] ==0 ? ago($row['creationDate']) : ago($row['statusChangedDate']);
			$list[$row['id']]['desc'] = $row['description'];
			$list[$row['id']]['order'] = $row['order_id'];
			$list[$row['id']]['important'] = $row['important'];
			$list[$row['id']]['tasktype'] = $row['tasktype'];
			$list[$row['id']]['status'] = $row['status'];
			$list[$row['id']]['reported'] = $row['reported'];
			$list[$row['id']]['status_date'] = date("d M, Y H:i", strtotime($row['statusChangedDate']));
			$list[$row['id']]['images'] = $this->getImagesByTaskId($row['id']);	
			$list[$row['id']]['pointTypeID'] = $row['pointTypeID'];
			$list[$row['id']]['point'] = $row['point'];
			
			if (count($files) > 0)
				$list[$row['id']]['images'] = implode("|", $files);
							
			if (!is_null($row['userid']))
				$list[$row['id']]['users'][$row['userid']] = $row['username'];
			else
				$list[$row['id']]['users'] = array();
			
			if (!is_null($row['projectid']))
				$list[$row['id']]['projects'][$row['projectid']] = $row['projectname'];
			else
				$list[$row['id']]['projects'] = array();

		}
 		mysqli_free_result($result);
		return $list;
	}
	
	function getTasksByProject($projectId){
		$query = "SELECT 
					t.*, 
					uauthor.nickname AS author, 
					p.id AS projectid, 
					p.name AS projectname, 
					u.id AS userid,
					u.fbuid AS fbuid,
					UPPER(u.nickName) AS username, 
					ut.owner , tp.pointTypeID, tp.point
				FROM task t
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author 
				WHERE p.archstate=0 and t.deleted=0 and t.projectId = %d AND t.status = 0
				ORDER BY t.important DESC, t.order_id";
		$result = mysqli_query($this->conn, $this->format($query, array($projectId)));
		
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['name'] = $row['name'];
			$list[$row['id']]['author'] = $row['author'];
			$list[$row['id']]['date'] = date("d M, Y H:i", strtotime($row['creationDate']));
			$list[$row['id']]['finish_date'] = $row['finishDate'];
			$list[$row['id']]['finish_date_left'] = timeLeft($row['finishDate']);
			$list[$row['id']]['dateago'] = ago($row['statusChangedDate']);
			$list[$row['id']]['desc'] = $row['description'];
			$list[$row['id']]['order'] = $row['order_id'];
			$list[$row['id']]['important'] = $row['important'];
			$list[$row['id']]['tasktype'] = $row['tasktype'];
			$list[$row['id']]['status'] = $row['status'];
			$list[$row['id']]['reported'] = $row['reported'];
			$list[$row['id']]['status_date'] = date("d M, Y H:i", strtotime($row['statusChangedDate']));
			$list[$row['id']]['images'] = $this->getImagesByTaskId($row['id']);
			$list[$row['id']]['pointTypeID'] = $row['pointTypeID'];
			$list[$row['id']]['point'] = $row['point'];			
							
			if (count($files) > 0)
				$list[$row['id']]['images'] = implode("|", $files);
			
			$list[$row['id']]['users'][$row['userid']] = $row['username'];
			$list[$row['id']]['img_users'][$row['userid']] =  $this->uModel->getImagesByUserId($row["userid"]);	
			$list[$row['id']]['id_users'][$row['userid']] =  $row['userid'];
			$list[$row['id']]['fb_users'][$row['userid']] =  $row['fbuid'];
			
			if ($row["owner"] == 1) {
				$list[$row["id"]]["owner"]["userid"] = $row["userid"];
				$list[$row["id"]]["owner"]["fbuid"] = $row["fbuid"];
				$list[$row["id"]]["owner"]["totaltaskscount"] = $this->getUserTotalTasksCount($row["userid"]);
				$list[$row["id"]]["owner"]["totaldonetaskscount"] = $this->getUserDoneTasksCount($row["userid"]);
			}
			
			$list[$row['id']]['totaltaskscount'][$row['userid']] = $this->getUserTotalTasksCount($row['userid']);
			$list[$row['id']]['todaydonetaskscount'][$row['userid']] = $this->getUserDoneTasksCount($row['userid']);
			
			if (!is_null($row['projectid']))
				$list[$row['id']]['projects'][$row['projectid']] = $row['projectname'];
			else
				$list[$row['id']]['projects'] = array();

		}
		
 		mysqli_free_result($result);
		return $list;		
	}
	
	function getDoneTasksByProject($projectId){
		$query = "SELECT t.*, uauthor.nickName AS author, p.id AS projectid, p.name AS projectname, u.id AS userid, UPPER(u.nickName) AS username FROM task t
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author 
				WHERE p.archstate=0 and t.deleted=0 and t.projectId = %d AND t.status = 1
				ORDER BY t.statusChangedDate DESC";
		$result = mysqli_query($this->conn, $this->format($query, array($projectId)));
		
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['name'] = $row['name'];
			$list[$row['id']]['author'] = $row['author'];
			$list[$row['id']]['date'] = date("d M, Y H:i", strtotime($row['creationDate']));
			$list[$row['id']]['finish_date'] = $row['finishDate'];
			$list[$row['id']]['finish_date_left'] = timeLeft($row['finishDate']);
			$list[$row['id']]['dateago'] = $row['status'] ==0 ? ago($row['creationDate']) : ago($row['statusChangedDate']);
			$list[$row['id']]['desc'] = $row['description'];
			$list[$row['id']]['order'] = $row['order_id'];
			$list[$row['id']]['important'] = $row['important'];
			$list[$row['id']]['tasktype'] = $row['tasktype'];
			$list[$row['id']]['status'] = $row['status'];
			$list[$row['id']]['reported'] = $row['reported'];
			$list[$row['id']]['status_date'] = date("d M, Y H:i", strtotime($row['statusChangedDate']));
			$list[$row['id']]['images'] = $this->getImagesByTaskId($row['id']);
					
			if (!is_null($row['userid']))
				$list[$row['id']]['users'][$row['userid']] = $row['username'];
			else
				$list[$row['id']]['users'] = array();
			
			if (!is_null($row['projectid']))
				$list[$row['id']]['projects'][$row['projectid']] = $row['projectname'];
			else
				$list[$row['id']]['projects'] = array();

		}
 		mysqli_free_result($result);
		return $list;		
	}
	
	function addUsersToTask($taskId, $userIdsList, $ownerId)
	{
		$query = "select userId, status from taskuser where taskId = %d";
		$result = mysqli_query($this->conn, $this->format($query, array($taskId)));
		$oldUids = array();
		$oldStatus = array();
		while($row = mysqli_fetch_assoc($result)) {
			$oldUids[$row['userId']] = $row['userId'];
			$oldStatus[$row['userId']]['status'] = $row['status'];
		}
 		mysqli_free_result($result);
		
		$this->deleteTaskFromUsers($taskId);
		foreach ($userIdsList as $userId){
			$userNew = $oldStatus[$userId]['status'];
			if(!in_array($userId, $oldUids)) {
				$userNew = 0;
			}
			$isOwner = ($userId == $ownerId) ? 1 : 0;
			$query = "INSERT INTO taskuser VALUES (%d, %d, %d, %d)";
			mysqli_query($this->conn, $this->format($query, array($taskId, $userId, $userNew, $isOwner)));
			if(!in_array($userId, $oldUids)) {
				$this->sendNewTaskMail($userId, $taskId);
			}
		}
	}
	
	function addUserToTask($taskId, $userId){
		$this->deleteTaskFromUsers($taskId);
		$query = "INSERT INTO taskuser VALUES(%d, %d, 0, 1)";
		$query = $this->format($query, array($taskId, $userId));
		mysqli_query($this->conn, $query);
	}	
	
	function sendNewTaskMail($userId, $taskId)	{
		$uDto = $this->uModel->getUserData($userId);

		$params['firstname'] = $uDto->getFirstName();
		$params['lastname'] = $uDto->getLastName();
		$params['querystr'] = "id=".$taskId;
		$params['_BESTNAME_'] = $uDto->getNickName();
		$params['_PATH_'] = SITE_URL_CLIENT;
		
		incSendMail($uDto->getEmail(), "newtask", $params);
		$yModel = new yammerModel();
		$yModel->incYammerDirectMessage($userId, "yammernewtask", $params);
	}

	function deleteTaskFromUsers($taskId){
		$query = "delete from taskuser where taskId=%d";
		$query = $this->format($query, array($taskId));	
		return $this->execute($query, null);
	}

	function deleteUnAssignedTasks(){
		$query = "delete from task where id not in (select taskId from taskuser);";
		return $this->execute($query, null);		
	}
	
	function getUserTasksByProject($prjId, $userId)
	{
		$query = "SELECT t.*, uauthor.nickName AS author, tp.pointTypeID, tp.point
				 FROM task t
				LEFT JOIN taskuser tu ON tu.taskId = t.id 
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN user u ON tu.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author 
				WHERE t.projectId = %d and t.deleted=0 AND u.id=%d
				ORDER BY t.important DESC, t.order_id";
		$result = mysqli_query($this->conn, $this->format($query, array($prjId, $userId)));				
		
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['name'] = $row['name'];
			$list[$row['id']]['author'] = $row['author'];
			$list[$row['id']]['date'] = $row['creationDate'];
			$list[$row['id']]['finish_date'] = $row['finishDate'];
			$list[$row['id']]['finish_date_left'] = timeLeft($row['finishDate']);
			$list[$row['id']]['dateago'] = $row['status'] ==0 ? ago($row['creationDate']) : ago($row['statusChangedDate']);
			$list[$row['id']]['desc'] = $row['description'];
			$list[$row['id']]['order'] = $row['order_id'];
			$list[$row['id']]['tasktype'] = $row['tasktype'];
			$list[$row['id']]['status'] = $row['status'];
			$list[$row['id']]['important'] = $row['important'];
			$list[$row['id']]['status_date'] = $row['statusChangedDate'];
			$list[$row['id']]['images'] = $this->getImagesByTaskId($row['id']);	
			$list[$row['id']]['pointTypeID'] = $row['pointTypeID'];
			$list[$row['id']]['point'] = $row['point'];
			
		}
 		mysqli_free_result($result);
		return $list;		
	}
	
	function getUserAllTasks($userId)
	{
		$query = "SELECT 
					t.*, 
					uauthor.nickName AS author, 
					p.id AS projectid, 
					p.name AS projectname, 
					u.id AS userid,
					u.fbuid AS fbuid,  
					CONCAT(u.firstName, ' ', u.lastName) AS username, 
					tp.pointTypeID, tp.point
					FROM task t
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author 
				WHERE p.archstate=0 AND t.deleted=0 AND t.status = 0 AND u.id = %d
				ORDER BY t.important DESC, t.order_id";
				
		$result = mysqli_query($this->conn, $this->format($query, array($userId)));
		
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['name'] = $row['name'];
			$list[$row['id']]['author'] = $row['author'];
			$list[$row['id']]['date'] = $row['creationDate'];
			$list[$row['id']]['finish_date'] = $row['finishDate'];
			$list[$row['id']]['finish_date_left'] = timeLeft($row['finishDate']);
			$list[$row['id']]['dateago'] = $row['status'] ==0 ? ago($row['creationDate']) : ago($row['statusChangedDate']);
			$list[$row['id']]['desc'] = $row['description'];
			$list[$row['id']]['order'] = $row['order_id'];
			$list[$row['id']]['status'] = $row['status'];
			$list[$row['id']]['reported'] = $row['reported'];
			$list[$row['id']]['tasktype'] = $row['tasktype'];
			$list[$row['id']]['important'] = $row['important'];
			$list[$row['id']]['status_date'] = $row['statusChangedDate'];
			$list[$row['id']]['images'] = $this->getImagesByTaskId($row['id']);	
			$list[$row['id']]['pointTypeID'] = $row['pointTypeID'];
			$list[$row['id']]['point'] = $row['point'];
			
			if (!is_null($row['userid']))
				$list[$row['id']]['users'][$row['userid']] = $row['username'];
			else
				$list[$row['id']]['users'] = array();
			
			if (!is_null($row['projectid']))
				$list[$row['id']]['projects'][$row['projectid']] = $row['projectname'];
			else
				$list[$row['id']]['projects'] = array();
		
		}
 		mysqli_free_result($result);
		return $list;
	}
	
	
	function getUserDailyTasks($userId, $date)
	{	
		$query = "SELECT 
					t.*, 
					dt.doneStatus,
					uauthor.nickName AS author, 
					p.id AS projectid, 
					p.name AS projectname, 
					u.id AS userid,
					u.fbuid AS fbuid,  
					CONCAT(u.firstName, ' ', u.lastName) AS username, 
					tp.pointTypeID, tp.point,
					ut.owner
					FROM dailytask dt
				LEFT JOIN task t ON t.id = dt.taskId	
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author
				WHERE p.archstate = 0 AND t.deleted = 0 AND u.id = %d AND dt.date=DATE('%s')
				ORDER BY t.status ASC";
				
		$result = mysqli_query($this->conn, $this->format($query, array($userId, $date)));
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['name'] = $row['name'];
			$list[$row['id']]['author'] = $row['author'];
			$list[$row['id']]['date'] = $row['creationDate'];
			$list[$row['id']]['finish_date'] = $row['finishDate'];
			$list[$row['id']]['finish_date_left'] = timeLeft($row['finishDate']);
			$list[$row['id']]['dateago'] = $row['status'] ==0 ? ago($row['creationDate']) : ago($row['statusChangedDate']);
			$list[$row['id']]['desc'] = $row['description'];
			$list[$row['id']]['order'] = $row['order_id'];
			$list[$row['id']]['status'] = $row['status'];
			$list[$row['id']]['reported'] = $row['reported'];
			$list[$row['id']]['tasktype'] = $row['tasktype'];
			$list[$row['id']]['important'] = $row['important'];
			$list[$row['id']]['status_date'] = $row['statusChangedDate'];
			$list[$row['id']]['images'] = $this->getImagesByTaskId($row['id']);	
			$list[$row['id']]['pointTypeID'] = $row['pointTypeID'];
			$list[$row['id']]['point'] = $row['point'];
			$list[$row['id']]['daily'] = $this->dailytaskModel->getDailyTaskDate($row['id']);;
			//$list[$row['id']]['owner'] = "1";

			if (!is_null($row['userid']))
				$list[$row['id']]['users'][$row['userid']] = $row['username'];
			else
				$list[$row['id']]['users'] = array();
			
			if (!is_null($row['projectid']))
				$list[$row['id']]['projects'][$row['projectid']] = $row['projectname'];
			else
				$list[$row['id']]['projects'] = array();
		
		}
 		mysqli_free_result($result);
		return $list;
	}
	
	
	function getUsersTasks()
	{
		$activeUser = $this->uModel->getActiveUser();
		
		$query = "SELECT 
					t.id AS tid,
					t.name AS tname, 
					t.description AS tdesc,
					t.reported AS treported,
					t.important AS timportant,
					t.tasktype AS ttasktype,
					t.creationDate AS creationDate,
					t.finishDate AS finishDate,
					t.statusChangedDate AS statusChangedDate,					
					p.id AS pid, 
					p.name AS pname, 
					u.id AS uid,
					u.nickName AS unick,
					u.fbuid AS fbuid,
					uauthor.nickName AS author,
					ut.owner, tp.pointTypeID, tp.point
				FROM task t
				LEFT JOIN project p ON t.projectId = p.id " . ($activeUser->getRole() == 2 ? "AND p.id IN (SELECT projectID FROM permissions WHERE userID = {$activeUser->getId()})" : "") . "
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author
				WHERE p.archstate=0 and t.deleted=0 and t.status = 0
				ORDER BY t.important DESC, t.order_id";
		
		$result = mysqli_query($this->conn, $this->format($query, array()));
		
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["pid"]]["pid"] = $task["pid"];
			$tasks[$task["pid"]]["name"] = $task["pname"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["date"] = date("d M, Y H:i", strtotime($task['creationDate']));
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["finish_date"] = $task['finishDate'];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["finish_date_left"] = timeLeft($task['finishDate']);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["dateago"] = ago($task['creationDate']);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["status_date"] = date("d M, Y H:i", strtotime($task['statusChangedDate']));
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["author"] = $task["author"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["important"] = $task["timportant"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["tasktype"] = $task["ttasktype"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["reported"] = $task["treported"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]['images'] = $this->getImagesByTaskId($task['tid']);	
			$tasks[$task["pid"]]["tasks"][$task["tid"]]['pointTypeID'] = $task['pointTypeID'];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]['point'] = $task['point'];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["daily"] = $this->dailytaskModel->getDailyTaskDate($task['tid']);
			
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["fbuid"] = $task["fbuid"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["image"] = $this->uModel->getImagesByUserId($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["totaltaskscount"] = $this->getUserTotalTasksCount($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["donetaskscount"] = $this->getUserDoneTasksCount($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["owner"] = $task["owner"];
		}
 		mysqli_free_result($result);
		return $tasks;
	}
	
	/**
	 * Getting all tasks wrapped in the projects
	 * @param $userId
	 * @return unknown_type
	 */
	function getUserTasks($userId = 0)
	{
		$query = "SELECT 
					t.id AS tid,
					t.name AS tname, 
					t.description AS tdesc,
					t.reported AS treported,
					t.important AS timportant,
					t.tasktype AS ttasktype,
					t.creationDate AS creationDate,
					t.finishDate AS finishDate,
					t.statusChangedDate AS statusChangedDate,
					p.id AS pid, 
					p.name AS pname, 
					u.id AS uid,
					u.nickName AS unick,
					u.fbuid AS fbuid,
					uauthor.nickName AS author,
					ut.owner, tp.pointTypeID, tp.point
				FROM task t
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author
				WHERE p.archstate=0 and t.deleted=0 and t.status = 0 AND t.id IN (SELECT taskId FROM taskuser WHERE userId = %d)
				ORDER BY t.important DESC, t.order_id";		
		$result = mysqli_query($this->conn, $this->format($query, array($userId)));

	
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["pid"]]["pid"] = $task["pid"];
			$tasks[$task["pid"]]["name"] = $task["pname"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["date"] = date("d M, Y H:i", strtotime($task['creationDate']));
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["finish_date"] = $task['finishDate'];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["finish_date_left"] = timeLeft($task['finishDate']);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["status_date"] = date("d M, Y H:i", strtotime($task['statusChangedDate']));			
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["dateago"] = ago($task['creationDate']);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["author"] = $task["author"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["reported"] = $task["treported"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["important"] = $task["timportant"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["tasktype"] = $task["ttasktype"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["daily"] = $this->dailytaskModel->getDailyTaskDate($task['tid']);
			
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["fbuid"] = $task["fbuid"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["image"] = $this->uModel->getImagesByUserId($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["totaltaskscount"] = $this->getUserTotalTasksCount($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["donetaskscount"] = $this->getUserDoneTasksCount($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["owner"] = $task["owner"];
			
			$tasks[$task["pid"]]["tasks"][$task["tid"]]['images'] =  $this->getImagesByTaskId($task['tid']);

			$tasks[$task["pid"]]["tasks"][$task["tid"]]['pointTypeID'] = $task['pointTypeID'];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]['point'] = $task['point'];	
			
		}
 		mysqli_free_result($result);
		return $tasks;
	}
	
	function getUserTotalTasksCount($userId)
	{
		$query = "
				SELECT 
					COUNT(t.id) AS tasks_count
				FROM task t
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				WHERE p.archstate = 0 and t.deleted=0 AND t.status = 0 AND ut.userId = %d";
		
		$result = mysqli_query($this->conn, $this->format($query, array($userId)));
		$task = mysqli_fetch_assoc($result);
 		mysqli_free_result($result);
		return $task["tasks_count"];
	}
	
	
	function getUserDoneTasksCount($userId)
	{
		$query = "
			SELECT 
				COUNT(t.id) AS tasks_count
			FROM task t
			LEFT JOIN project p ON t.projectId = p.id
			LEFT JOIN taskuser ut ON ut.taskId = t.id
			WHERE p.archstate = 0 and t.deleted=0 and t.status = 1 AND ut.userId = %d AND DAY(t.statusChangedDate) = DAY(NOW())
		";
		
		$result = mysqli_query($this->conn, $this->format($query, array($userId)));
		$task = mysqli_fetch_assoc($result);
 		mysqli_free_result($result);
		return $task["tasks_count"];
	}
	
	
	function getUserDoneTasks($userId, $period = "")
	{
		$dateRange = getDatesRange($period);
		
		$query = "SELECT 
					t.id AS tid,
					t.projectId AS pid,
					t.name AS tname, 
					t.description AS tdesc,
					t.finishDate AS finishDate,
					t.statusChangedDate AS tstatusChangedDate,
					u.id AS uid,
					u.nickName AS unick
				FROM task t
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN project p ON t.projectId = p.id
				WHERE p.archstate=0 and t.deleted=0 and t.status = 1 
					" . (is_null($dateRange) ? "" : " AND t.statusChangedDate BETWEEN '%s' AND '%s' ") . " 
					AND t.id IN (SELECT taskId FROM taskuser WHERE userId = %d)
				ORDER BY t.statusChangedDate DESC";
		
		if (is_null($dateRange))
		{
			$result = mysqli_query($this->conn, $this->format($query, array($userId)));
		}
		else
		{
			$result = mysqli_query($this->conn, $this->format($query, array($dateRange["start"], $dateRange["end"], $userId)));
		}
		
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["tid"]]["id"] = $task["tid"];
			$tasks[$task["tid"]]["pid"] = $task["pid"];
			$tasks[$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["tid"]]["finish_date"] = $task["finishDate"];
			$tasks[$task["tid"]]["finish_date_left"] = timeLeft($task["finishDate"]);
			$tasks[$task["tid"]]["dateago"] = ago($task["tstatusChangedDate"]);
			$tasks[$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task["tid"]]['images'] =  $this->getImagesByTaskId($task['tid']);		
		}
 		mysqli_free_result($result);
		return $tasks;
	}
	
	function getDoneTasksByUserList($userIds, $period = "")
	{
		$dateRange = getDatesRange($period);
		
		$userIds = str_replace("|", ",", $userIds);
		
		$query = "SELECT 
					t.id AS tid,
					t.projectId AS pid,
					t.name AS tname, 
					t.description AS tdesc,
					t.finishDate AS finishDate,
					t.statusChangedDate AS tstatusChangedDate,
					u.id AS uid,
					u.nickName AS unick
				FROM task t
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN project p ON t.projectId = p.id
				WHERE p.archstate=0 and t.deleted=0 and t.status = 1 
					" . (is_null($dateRange) ? "" : " AND t.statusChangedDate BETWEEN '%s' AND '%s' ") . " 
					AND t.id IN (SELECT taskId FROM taskuser WHERE userId IN (%s))
				ORDER BY t.statusChangedDate DESC";
		
		
		if (is_null($dateRange))
		{
			$result = mysqli_query($this->conn, $this->format($query, array($userIds)));
		}
		else
		{
			$result = mysqli_query($this->conn, $this->format($query, array($dateRange["start"], $dateRange["end"], $userIds)));
		}
		
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["tid"]]["id"] = $task["tid"];
			$tasks[$task["tid"]]["pid"] = $task["pid"];
			$tasks[$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["tid"]]["finish_date"] = $task["finishDate"];
			$tasks[$task["tid"]]["finish_date_left"] = timeLeft($task["finishDate"]);
			$tasks[$task["tid"]]["dateago"] = ago($task["tstatusChangedDate"]);
			$tasks[$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task["tid"]]['images'] =  $this->getImagesByTaskId($task['tid']);		
		}
 		mysqli_free_result($result);
		return $tasks;
	}
	
	function getUsersDoneTasks($period = "")
	{
		$dateRange = getDatesRange($period);
		
		$query = "SELECT 
					t.id AS tid,
					t.projectId AS pid,
					t.name AS tname, 
					t.description AS tdesc,
					t.finishDate AS finishDate,
					t.statusChangedDate AS tstatusChangedDate,
					u.id AS uid,
					u.nickName AS unick 
				FROM task t
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN project p ON t.projectId = p.id
				WHERE p.archstate=0 and t.deleted=0 and t.status = 1 
					" . (is_null($dateRange) ? "" : " AND t.statusChangedDate BETWEEN '%s' AND '%s' ") . " 
				ORDER BY t.statusChangedDate DESC";
		
		if (is_null($dateRange))
		{
			$result = mysqli_query($this->conn, $this->format($query, array()));
		}
		else
		{
			$result = mysqli_query($this->conn, $this->format($query, array($dateRange["start"], $dateRange["end"])));
		}
		
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["tid"]]["id"] = $task["tid"];
			$tasks[$task["tid"]]["pid"] = $task["pid"];
			$tasks[$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["tid"]]["finish_date"] = $task["finishDate"];
			$tasks[$task["tid"]]["finish_date_left"] = timeLeft($task["finishDate"]);
			$tasks[$task["tid"]]["dateago"] = ago($task["tstatusChangedDate"]);
			$tasks[$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task['tid']]['images'] = $this->getImagesByTaskId($task['tid']);					
		}
 		mysqli_free_result($result);
		return $tasks;
	}

	
	function getTask($taskId)
	{
		$query = "SELECT 
					t.id AS tid,
					t.name AS tname, 
					t.creationDate AS tdate,
					t.finishDate AS finishDate,
					t.description AS tdesc,
					t.reported AS treported,
					t.important AS timportant,
					t.tasktype AS ttasktype,
					t.status AS tstatus,
					p.id AS pid, 
					p.name AS pname, 
					u.id AS uid,
					u.nickName AS unick, tp.pointTypeID, tp.point
				FROM task t
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				WHERE p.archstate=0 and t.deleted=0 and t.id = %d";		
		$result = mysqli_query($this->conn, $this->format($query, array($taskId)));
		
		$tasks = array();
		while ($row = mysqli_fetch_assoc($result))
		{
			$tasks['id'] = $row['tid'];
			$tasks['name'] = $row['tname'];
			$tasks['date'] = $row['tdate'];
			$tasks['finish_date'] = $row['finishDate'];
			$tasks['finish_date_left'] = timeLeft($row['finishDate']);
			$tasks['dateago'] = ago($row['tdate']);
			$tasks['desc'] = $row['tdesc'];
			$tasks['reported'] = $row['treported'];
			$tasks['important'] = $row['timportant'];
			$tasks['tasktype'] = $row['ttasktype'];
			$tasks['status'] = $row['tstatus'];
			$tasks['images'] = $this->getImagesByTaskId($row['tid']);
			$tasks['pointTypeID'] = $row['pointTypeID'];
			$tasks['point'] = $row['point'];			
						
			if (!is_null($row['uid']))
				$tasks['users'][$row['uid']] = $row['unick'];
			else
				$tasks['users'] = array();
			
			if (!is_null($row['pid']))
				$tasks['projects'][$row['pid']] = $row['pname'];
			else
				$tasks['projects'] = array();
		}
 		mysqli_free_result($result);
		return $tasks;
	}
	
	function getTaskRelatedUserList($taskId){
		$query = "select distinct u.* from task t  
				left join taskuser tu on t.id=tu.taskId 
				left join user u on u.id = t.author or tu.userId = u.id 
				where t.id = %d  and t.deleted=0";
		$result = mysqli_query($this->conn, $this->format($query, array($taskId)));
		
				
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['firstname'] = $row['firstname'];
			$list[$row['id']]['lastname'] = $row['lastname'];
			$list[$row['id']]['nickname'] = $row['nickname'];
			$list[$row['id']]['email'] = $row['email'];
		}
 		mysqli_free_result($result);
		return $list;		
	}

	function getUnreportedTaskListUser($userId, $projectId, $status) {
		
	}

	function getUnreportedTaskList($userId, $projectId, $status) {
		$query = "select t.id AS id,
					t.name AS name, 
					t.creationDate AS creationDate,
					t.finishDate AS finishDate,
					t.description AS description,
					t.reported AS reported,
					t.important AS important,
					t.tasktype AS tasktype,
					t.status AS status,
					t.order_id AS order_id,
					t.statusChangedDate AS statusChangedDate,
					u.id AS uid,
					u.nickName AS unick,
					uauthor.nickName AS author, 
					tp.pointTypeID, tp.point
				from task t
				left join taskuser tu on t.id = tu.taskId
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				left join user u on tu.userId = u.id 
				LEFT JOIN user uauthor ON uauthor.id = t.author
				where t.status = 0 and t.deleted=0 and u.id in (select tu.userId from taskuser tu where tu.taskId=t.id) and ((select tu.status from taskuser tu where tu.taskId=t.id and tu.userId=%d)=%d or (uauthor.id=%d and ((select count(*) from taskuser tu where tu.taskId=t.id and tu.status=1)!=0)=%d)) and t.reported = 0 and t.projectId = %d
				ORDER BY t.important desc, t.tasktype 
				";
		$result = mysqli_query($this->conn, $this->format($query, array($userId, $status, $userId, $status, $projectId)));
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['name'] = $row['name'];
			$list[$row['id']]['date'] = date("d M, Y H:i", strtotime($row['creationDate']));
			$list[$row['id']]['finish_date'] = $row['finishDate'];
			$list[$row['id']]['finish_date_left'] = timeLeft($row['finishDate']);
			$list[$row['id']]['dateago'] = ago($row['creationDate']);
			$list[$row['id']]['desc'] = $row['description'];
			$list[$row['id']]['order'] = $row['order_id'];
			$list[$row['id']]['reported'] = $row['reported'];
			$list[$row['id']]['important'] = $row['important'];
			$list[$row['id']]['color'] = $this->importanceStatus[$row['important']-1][2];
			$list[$row['id']]['tasktype'] = $row['tasktype'];
			$list[$row['id']]['status'] = $row['status'];
			$list[$row['id']]['author'] = $row['author'];
			$list[$row['id']]['status_date'] = date("d M, Y H:i", strtotime($row['statusChangedDate']));
			$list[$row['id']]['users'][$row['uid']]['id'] = $row['uid'];
			$list[$row['id']]['users'][$row['uid']]['name'] = $row['unick'];
			$list[$row['id']]['pointTypeID'] = $row['pointTypeID'];
			$list[$row['id']]['point'] = $row['point'];	
		}
 		mysqli_free_result($result);
		return $list;		
	}
	
	function getSimpleTaskById($id)
	{
		$query = "SELECT * FROM task WHERE id=%d";
		$query = $this->format($query, array($id));	
		$result = mysqli_query($this->conn, $query);
		$row = mysqli_fetch_assoc($result);	
 		mysqli_free_result($result);
		return $row;
	}
	
	function getNewOrderByProject($projectId){		
		$query = "SELECT order_id FROM task WHERE projectId=%d AND status=0 and deleted=0 ORDER BY order_id DESC LIMIT 0,1";
		$query = $this->format($query, array($projectId));	
		$result = mysqli_query($this->conn, $query);	
		$row = mysqli_fetch_assoc($result);
 		mysqli_free_result($result);
		return $row['order_id']+1;
	}
	
	function updateTaskListOrder($orderList){
		for ($i = 0; $i < count($orderList); $i++){
			$query = "update task set order_id = %d where id = %d";
			$query = $this->format($query, array($i+1, $orderList[$i]));
			$res = mysqli_query($this->conn, $query);
		}
	}	

	function getImagesByTaskId($taskId)
	{
		$dirPath = "images/".$taskId;
		$filelist = array();
	    if(is_dir($dirPath) ){
	        if ($dh = opendir($dirPath)) {
		        while (($file = readdir($dh)) !== false) {
		        	if (($file != '.')&&($file != '..')&&($file != 'Thumbs.db')&&($file != '.svn'))
		           		$filelist[] =  $file;
		        }
		        closedir($dh);
		    }
	    }
	    return implode("|", $filelist);
	}

	function notifyTasksOwnersLastTask($taskId)
	{
		$userList = $this->getTaskRelatedUserList($taskId);
		
		foreach ($userList as $user) {
			if($this->getCountOfUserActiveTasks($user['id']) == 0)
				$this->sendNoTaskEmail($user['id']);
		}
	}
	
	function sendNoTaskEmail($userId) {
		$uDto = $this->uModel->getUserData($userId);

		$params['_BESTNAME_'] = $uDto->getNickName();
		$params['firstname'] = $uDto->getFirstName();
		$params['lastname'] = $uDto->getLastName();
		$params['querystr'] = "";
		$params['_PATH_'] = SITE_URL_CLIENT;
		
		$yModel = new yammerModel();
		$usrMailSent = incSendMail($uDto->getEmail, "nomoretasksuser", $params);
		$yModel->incYammerDirectMessage($userId, "yammernomoretasksuser", $params);
		
		$params['firstname'] = "Admins";
		$params['lastname'] = "";
		
		$admMailSent = incSendMail(EMAIL_ADMIN_FOR_NOTASK, "nomoretasksadm", $params);
		
		$adminlist = explode(',', YAMMER_ADMINS);

		foreach ($adminlist as $adminid)
			$yModel->incYammerDirectMessage($adminid, "yammernomoretasksadm", $params);

		return $admMailSent && $usrMailSent;
	}	
	
	function getCountOfUserActiveTasks($userId)
	{
		$query = "select count(*) as count from task t left join taskuser tu on t.id = tu.taskId where t.status = 0 and t.deleted=0 and tu.userId = %d";
		$query = $this->format($query, array($userId));	
		$result = mysqli_query($this->conn, $query);	
		$row = mysqli_fetch_assoc($result);
		return $row['count'];
	}

	/*
	 * $statusNewUpdated 
	 * 0: new tasks count is needed, 1: count of updated tasks.
	 */
	function getUnreportedTasksCount($userId, $projectId, $statusNewUpdated)
	{
		$query = "select count(*) as count from (select sum(tu.status)>0 as tstatus from task t
					left join taskuser tu on tu.taskId = t.id
					left join user u on u.id = tu.userId
					where t.projectId=%d and t.status=0 and t.deleted=0 and t.reported=0 and 
					(tu.userId=%d or t.author=%d) group by t.id) tmp where tmp.tstatus=%d";
		$query = $this->format($query, array($projectId, $userId, $userId, $statusNewUpdated));	
		$result = mysqli_query($this->conn, $query);	
		$row = mysqli_fetch_assoc($result);
 		mysqli_free_result($result);
		return $row['count'];
	}	

	function getUserDoneTasksInPrj($userId, $period = "", $projId)
	{
		$activeUser = $this->uModel->getActiveUser();
		
		$dateRange = getDatesRange($period);
		
		$query = "SELECT 
					t.id AS tid,
					t.projectId AS pid,
					t.name AS tname, 
					t.description AS tdesc,
					t.statusChangedDate AS tstatusChangedDate,
					t.creationDate AS creationDate,
					t.finishDate AS finishDate, 
					u.id AS uid,
					u.nickName AS unick,
					t.reported AS treported,
					t.important AS timportant,
					t.tasktype AS ttasktype,
					uauthor.nickName AS author
				FROM task t
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author 
				LEFT JOIN project p ON t.projectId = p.id " . ($activeUser->getRole() == 2 ? " AND p.id IN (SELECT projectID FROM permissions WHERE userID = {$activeUser->getUserId()})" : "") . "
				WHERE p.archstate=0 and t.deleted=0 and t.status = 1 
					" . (is_null($dateRange) ? "" : " AND t.statusChangedDate BETWEEN '%s' AND '%s' ") . " 
					AND t.id IN (SELECT taskId FROM taskuser WHERE userId = %d) 
					" . ($projId == 0 ? " " : " AND t.projectId=%d " ). "
				ORDER BY t.statusChangedDate DESC";
		
		$format_array = array();
		if (!is_null($dateRange))
			$format_array = array_merge($format_array, array($dateRange["start"], $dateRange["end"]));
		$format_array = array_merge($format_array, array($userId));
		if ($projId != 0)
			$format_array = array_merge($format_array, array($projId));
					
		
		$result = mysqli_query($this->conn, $this->format($query, $format_array));
		
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["tid"]]["id"] = $task["tid"];
			$tasks[$task["tid"]]["pid"] = $task["pid"];
			$tasks[$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["tid"]]["author"] = $task["author"];
			$tasks[$task["tid"]]["reported"] = $task["treported"];
			$tasks[$task["tid"]]["important"] = $task["timportant"];
			$tasks[$task["tid"]]["tasktype"] = $task["ttasktype"];
			$tasks[$task["tid"]]["date"] = date("d M, Y H:i", strtotime($task['creationDate']));
			$tasks[$task["tid"]]["finish_date"] = $task['finishDate'];
			$tasks[$task["tid"]]["finish_date_left"] = timeLeft($task['finishDate']);
			$tasks[$task["tid"]]["status_date"] = date("d M, Y H:i", strtotime($task['tstatusChangedDate']));			
			$tasks[$task["tid"]]["dateago"] = ago($task["tstatusChangedDate"]);
			$tasks[$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task['tid']]['images'] = $this->getImagesByTaskId($task['tid']);					
			$tasks[$task["tid"]]["users"][$task["uid"]]["image"] = $this->uModel->getImagesByUserId($task["uid"]);
		}
 		mysqli_free_result($result);
		return $tasks;
	}
	
	function getDoneTasksByUserListInPrj($userIds, $period = "", $projId)
	{
		$activeUser = $this->uModel->getActiveUser();
		
		$dateRange = getDatesRange($period);
		$userIds = str_replace("|", ",", $userIds);
		
		$query = "SELECT 
					t.id AS tid,
					t.projectId AS pid,
					t.name AS tname, 
					t.description AS tdesc,
					t.statusChangedDate AS tstatusChangedDate,
					t.creationDate AS creationDate, 
					t.finishDate AS finishDate,
					u.id AS uid,
					u.nickName AS unick,
					t.reported AS treported,
					t.important AS timportant,
					t.tasktype AS ttasktype,
					uauthor.nickName AS author
				FROM task t
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author 
				LEFT JOIN project p ON t.projectId = p.id " . ($activeUser->getRole() == 2 ? " AND p.id IN (SELECT projectID FROM permissions WHERE userID = {$activeUser->getId()})" : "") . "
				WHERE p.archstate=0 and t.deleted=0 and t.status = 1 
					" . (is_null($dateRange) ? "" : " AND t.statusChangedDate BETWEEN '%s' AND '%s' ") . " 
					AND t.id IN (SELECT taskId FROM taskuser WHERE userId IN (%s)) 
					" . ($projId == 0 ? " " : " AND t.projectId=%d " ). "
				ORDER BY t.statusChangedDate DESC";
		
		$format_array = array();
		if (!is_null($dateRange))
			$format_array = array_merge($format_array, array($dateRange["start"], $dateRange["end"]));
		$format_array = array_merge($format_array, array($userIds));
		if ($projId != 0)
			$format_array = array_merge($format_array, array($projId));
					
		$result = mysqli_query($this->conn, $this->format($query, $format_array));
		
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["tid"]]["id"] = $task["tid"];
			$tasks[$task["tid"]]["pid"] = $task["pid"];
			$tasks[$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["tid"]]["author"] = $task["author"];
			$tasks[$task["tid"]]["reported"] = $task["treported"];
			$tasks[$task["tid"]]["important"] = $task["timportant"];
			$tasks[$task["tid"]]["tasktype"] = $task["ttasktype"];
			$tasks[$task["tid"]]["date"] = date("d M, Y H:i", strtotime($task['creationDate']));
			$tasks[$task["tid"]]["finish_date"] = $task['finishDate'];
			$tasks[$task["tid"]]["finish_date_left"] = timeLeft($task['finishDate']);
			$tasks[$task["tid"]]["status_date"] = date("d M, Y H:i", strtotime($task['tstatusChangedDate']));			
			$tasks[$task["tid"]]["dateago"] = ago($task["tstatusChangedDate"]);
			$tasks[$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task['tid']]['images'] = $this->getImagesByTaskId($task['tid']);					
			$tasks[$task["tid"]]["users"][$task["uid"]]["image"] = $this->uModel->getImagesByUserId($task["uid"]);
		}
 		mysqli_free_result($result);
		return $tasks;
	}

	function getUsersDoneTasksInPrj($period = "", $projId)
	{
		$activeUser = $this->uModel->getActiveUser();
		
		$dateRange = getDatesRange($period);
		
		$query = "SELECT 
					t.id AS tid,
					t.projectId AS pid,
					t.name AS tname, 
					t.description AS tdesc,
					t.statusChangedDate AS tstatusChangedDate,
					t.creationDate AS creationDate, 
					t.finishDate AS finishDate,
					u.id AS uid,
					u.nickName AS unick, 
					t.reported AS treported,
					t.important AS timportant,
					t.tasktype AS ttasktype,
					uauthor.nickName AS author
				FROM task t
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author 
				LEFT JOIN project p ON t.projectId = p.id " . ($activeUser->getRole() == 2 ? " AND p.id IN (SELECT projectID FROM permissions WHERE userID = {$activeUser->getId()})" : "") . "
				WHERE p.archstate=0 and t.deleted=0 and t.status = 1 
					" . (is_null($dateRange) ? "" : " AND t.statusChangedDate BETWEEN '%s' AND '%s' ") . " 
					" . ($projId == 0 ? " " : " AND t.projectId=%d " ). "
				ORDER BY t.statusChangedDate DESC";
		
		$format_array = array();
		if (!is_null($dateRange))
			$format_array = array_merge($format_array, array($dateRange["start"], $dateRange["end"]));
		if ($projId != 0)
			$format_array = array_merge($format_array, array($projId));
		
		$query = $this->format($query, $format_array);
		$result = mysqli_query($this->conn, $query);
		
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["tid"]]["id"] = $task["tid"];
			$tasks[$task["tid"]]["pid"] = $task["pid"];
			$tasks[$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["tid"]]["author"] = $task["author"];
			$tasks[$task["tid"]]["reported"] = $task["treported"];
			$tasks[$task["tid"]]["important"] = $task["timportant"];
			$tasks[$task["tid"]]["tasktype"] = $task["ttasktype"];
			$tasks[$task["tid"]]["date"] = date("d M, Y H:i", strtotime($task['creationDate']));
			$tasks[$task["tid"]]["finish_date"] = $task['finishDate'];
			$tasks[$task["tid"]]["finish_date_left"] = timeLeft($task['finishDate']);
			$tasks[$task["tid"]]["status_date"] = date("d M, Y H:i", strtotime($task['tstatusChangedDate']));			
			$tasks[$task["tid"]]["dateago"] = ago($task["tstatusChangedDate"]);
			$tasks[$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task['tid']]['images'] = $this->getImagesByTaskId($task['tid']);					
			$tasks[$task["tid"]]["users"][$task["uid"]]["image"] = $this->uModel->getImagesByUserId($task["uid"]);
		}
 		mysqli_free_result($result);
		return $tasks;
	}
	
	function getUsersTasksCount($projectId, $period, $taskStatus)
	{
		$dateRange = getDatesRange($period);
		
		$query = "SELECT u.*, COUNT(tu.taskId) AS tasks_count FROM user AS u
				LEFT JOIN taskuser AS tu ON u.id = tu.userId
				LEFT JOIN task AS t ON t.id = tu.taskId and t.deleted=0
				LEFT JOIN project AS p ON t.projectId = p.id
				WHERE " . ($taskStatus == 0 ? "(t.status = %d AND p.archstate = 0)" : " (t.status = %d)" ). "
					" . (is_null($dateRange) ? "" : " AND t.statusChangedDate BETWEEN '%s' AND '%s' ") . " 
					" . ($projectId == 0 ? " " : " AND t.projectId=%d " ). "
				GROUP BY u.id
				ORDER BY tasks_count DESC";
		
		$format_array = array($taskStatus);
		if (!is_null($dateRange))
			$format_array = array_merge($format_array, array($dateRange["start"], $dateRange["end"]));
		if ($projectId != 0)
			$format_array = array_merge($format_array, array($projectId));
					
		
		$result = mysqli_query($this->conn, $this->format($query, $format_array));
		
		$users = array();
		while ($user = mysqli_fetch_assoc($result))
		{
			$users[$user["id"]]["id"] = $user["id"];
			$users[$user["id"]]["nickname"] = $user["nickName"];
			$users[$user["id"]]["tasks_count"] = $user["tasks_count"];
		}
		
 		mysqli_free_result($result);
		return $users;
	}
	
	function getTasksProjectName($taskId)
	{
		$query = "SELECT p.name AS pname FROM task t
				LEFT JOIN project p ON t.projectId = p.id
				WHERE t.id = %d and t.deleted=0";		
		$result = mysqli_query($this->conn, $this->format($query, array($taskId)));

		$retName = "";
		if ($row = mysqli_fetch_assoc($result))
		{
			$retName = $row['pname'];
		}
	 	mysqli_free_result($result);
		return $retName;
	}
	
	function getOwner($taskId)
	{
		$query = "SELECT tu.userId as ownerId FROM task t
				LEFT JOIN taskuser tu ON tu.taskId = t.id and tu.owner=1 
				WHERE t.id = %d and t.deleted=0";		
		$result = mysqli_query($this->conn, $this->format($query, array($taskId)));
		
		$retVal = null;
		if ($row = mysqli_fetch_assoc($result))
		{
			$retVal = $this->uModel->getUserData($row['ownerId']);
		}
	 	mysqli_free_result($result);
		return $retVal;
	}
	
	function moveTaskToProject($taskId, $newProjectId)
	{
		$query = "UPDATE task SET projectId=%d WHERE id = %d";		
		return mysqli_query($this->conn, $this->format($query, array($newProjectId, $taskId)));
	}
	
	function getNotificationList($dateFrom, $userId)
	{
		$query = "SELECT 
					t.id AS tid,
					t.name AS tname, 
					t.description AS tdesc,
					t.reported AS treported,
					t.important AS timportant,
					t.tasktype AS ttasktype,
					t.creationDate AS creationDate,
					t.deleteDate AS deleteDate,
					t.updateDate AS updateDate,
					t.finishDate AS finishDate,
					t.statusChangedDate AS statusChangedDate,
					p.archStateChangeDate AS archStateChangeDate,
					t.deleted AS deleted,
					t.status AS status,
					p.id AS pid, 
					p.name AS pname,
					p.archstate AS archstate, 
					u.id AS uid,
					u.nickName AS unick,
					u.fbuid AS fbuid,
					uauthor.nickName AS author,
					ut.owner, tp.pointTypeID, tp.point
				FROM task t
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				LEFT JOIN user uauthor ON uauthor.id = t.author
				WHERE t.id IN (SELECT taskId FROM taskuser WHERE userId = %d) 
				and (t.creationDate >='".$dateFrom."' or t.updateDate >='".$dateFrom."' 
				or t.deleteDate >='".$dateFrom."' or t.statusChangedDate >='".$dateFrom."'
				or p.archStateChangeDate >='".$dateFrom."')
				ORDER BY t.important DESC, t.order_id";		
		$result = mysqli_query($this->conn, $this->format($query, array($userId)));
		
		$tasks = array();
		while ($task = mysqli_fetch_assoc($result))
		{
			$tasks[$task["pid"]]["pid"] = $task["pid"];
			$tasks[$task["pid"]]["name"] = $task["pname"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["name"] = $task["tname"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["date"] = date("d M, Y H:i", strtotime($task['creationDate']));
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["finish_date"] = $task['finishDate'];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["finish_date_left"] = timeLeft($task['finishDate']);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["status_date"] = date("d M, Y H:i", strtotime($task['statusChangedDate']));			
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["dateago"] = ago($task['creationDate']);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["desc"] = $task["tdesc"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["author"] = $task["author"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["reported"] = $task["treported"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["important"] = $task["timportant"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["tasktype"] = $task["ttasktype"];
			
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["id"] = $task["uid"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["fbuid"] = $task["fbuid"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["name"] = $task["unick"];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["image"] = $this->uModel->getImagesByUserId($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["totaltaskscount"] = $this->getUserTotalTasksCount($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["donetaskscount"] = $this->getUserDoneTasksCount($task["uid"]);
			$tasks[$task["pid"]]["tasks"][$task["tid"]]["users"][$task["uid"]]["owner"] = $task["owner"];
			
			$tasks[$task["pid"]]["tasks"][$task["tid"]]['images'] =  $this->getImagesByTaskId($task['tid']);

			$tasks[$task["pid"]]["tasks"][$task["tid"]]['pointTypeID'] = $task['pointTypeID'];
			$tasks[$task["pid"]]["tasks"][$task["tid"]]['point'] = $task['point'];	


			if($task["creationDate"] >= $dateFrom)
				$tasks[$task["pid"]]["tasks"][$task["tid"]]['ntype'] = "added";	
			if($task["updateDate"] >= $dateFrom)
				$tasks[$task["pid"]]["tasks"][$task["tid"]]['ntype'] = "updated";	
			if($task["statusChangedDate"] >= $dateFrom)
			{
				if($task["status"] == 1)
					$tasks[$task["pid"]]["tasks"][$task["tid"]]['ntype'] = "done";
				else	
					$tasks[$task["pid"]]["tasks"][$task["tid"]]['ntype'] = "undone";
			}
			if($task["deleteDate"] >= $dateFrom && $task["deleted"] == 1)
				$tasks[$task["pid"]]["tasks"][$task["tid"]]['ntype'] = "deleted";	
			if($task["archStateChangeDate"] >= $dateFrom)
			{
				if($task["archstate"] == 1)
					$tasks[$task["pid"]]["tasks"][$task["tid"]]['ntype'] = "archived";
				else	
					$tasks[$task["pid"]]["tasks"][$task["tid"]]['ntype'] = "unarchived";
			}
		}
	 	mysqli_free_result($result);
		return $tasks;
	}

	function getTaskForYammerMessage($taskId)
	{
		$query = "SELECT 
					t.id AS tid,
					t.name AS tname, 
					t.creationDate AS tdate,
					t.finishDate AS finishDate,
					t.description AS tdesc,
					t.reported AS treported,
					t.important AS timportant,
					t.tasktype AS ttasktype,
					t.status AS tstatus,
					p.id AS pid, 
					p.name AS pname, 
					u.id AS uid,
					u.nickName AS unick, 
					tp.pointTypeID AS tpointTypeID, 
					tp.point AS tpoint
				FROM task t
				LEFT JOIN project p ON t.projectId = p.id
				LEFT JOIN taskuser ut ON ut.taskId = t.id
				LEFT JOIN taskpoint tp ON tp.taskId = t.id
				LEFT JOIN user u ON ut.userId = u.id
				WHERE p.archstate=0 and t.id = %d";		
		$result = mysqli_query($this->conn, $this->format($query, array($taskId)));
		
		$task = array();
		while ($row = mysqli_fetch_assoc($result))
		{
			$task['id'] = $row['tid'];
			$task['name'] = $row['tname'];
			$task['date'] = $row['tdate'];
			$task['finish_date'] = $row['finishDate'];
			$task['finish_date_left'] = timeLeft($row['finishDate']);
			$task['dateago'] = ago($row['tdate']);
			$task['desc'] = $row['tdesc'];
			$task['reported'] = $row['treported'];
			$task['important'] = $row['timportant'];
			$task['tasktype'] = $row['ttasktype'];
			$task['status'] = $row['tstatus'];
			$task['images'] = $this->getImagesByTaskId($row['tid']);
			$task['pointTypeID'] = $row['tpointTypeID'];
			$task['tpoint'] = $row['tpoint'];
			$task['pName'] = $row['pname'];
			
			if (!is_null($row['uid']))
				$task['users'][$row['uid']] = $row['unick'];
			else
				$task['users'] = array();
		}
	 	mysqli_free_result($result);
		return $task;
	}
	
}
?>
